
#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "-------分别抽取上个表中的三各表的数据，分别上卷



-----报名相关数据
WITH signup_tmp AS (
    SELECT itcast_school_id,
           itcast_school_name,
           itcast_subject_id,
           itcast_subject_name,
           line_type,
           origin_channel,
           department_id,
           department_name,
           group_type,
           group_type_signup_count,
           pc_date_type AS day_type,
           substring(pc_date_type,1,7) AS month_type,
           substring(pc_date_type,1,4) AS year_type,
           dt
    FROM oe_dws.dws_signup_message1
    WHERE pc_date_type IS NOT NULL
)
select
       CASE
            WHEN grouping(year_type,month_type,day_type) = 0 THEN 'day'
            WHEN grouping(year_type,month_type) = 0 THEN 'month'
            WHEN grouping(year_type) = 0 THEN 'year'
            ELSE 'other' END AS date_type,
       group_type AS group_type_old,
        CASE
                    when grouping(line_type,itcast_school_id,itcast_subject_id) = 0  THEN 'line_school_subject'
                    WHEN grouping(line_type,itcast_subject_id) = 0 THEN 'line_subject'
                    WHEN grouping(line_type,itcast_school_id) = 0 THEN 'line_school'
                    when grouping(itcast_school_id) = 0 THEN 'school'
                    WHEN grouping(line_type,origin_channel) = 0 THEN 'line_origin'
                    WHEN grouping(line_type,department_id) = 0 THEN 'line_dept'
                    WHEN grouping(year_type) = 0 THEN 'all'
                    ELSE 'other' END AS group_type_new,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       line_type,
       origin_channel,
       department_id,
       department_name,
       sum(group_type_signup_count) AS group_type_signup_count,        ----计算
       day_type,
       month_type,
       year_type,
       '2023-05-30' AS dt
from signup_tmp
group by
grouping SETS (
    --年
        (year_type),
        (year_type,itcast_school_id,itcast_school_name,group_type),                         --日期+学校
        (year_type,line_type,itcast_school_id,itcast_school_name,group_type),               --日期+线上线下+学校
        (year_type,line_type,itcast_subject_id,itcast_subject_name,group_type),             --日期+线上线下+学科
        (year_type,line_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,group_type),      --日期+线上线下+学校+学科
        (year_type,line_type,origin_channel,group_type),                             --日期+线上线下+来源渠道
        (year_type,line_type,department_id,department_name,group_type),           --日期+线上线下+咨询中心
        --月
        (year_type,month_type,itcast_school_id,itcast_school_name,group_type),                         --日期+学校
        (year_type,month_type,line_type,itcast_school_id,itcast_school_name,group_type),               --日期+线上线下+学校
        (year_type,month_type,line_type,itcast_subject_id,itcast_subject_name,group_type),             --日期+线上线下+学科
        (year_type,month_type,line_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,group_type),      --日期+线上线下+学校+学科
        (year_type,month_type,line_type,origin_channel,group_type),                             --日期+线上线下+来源渠道
        (year_type,month_type,line_type,department_id,department_name,group_type),           --日期+线上线下+咨询中心
        --天
        (year_type,month_type,day_type,itcast_school_id,itcast_school_name,group_type),                         --日期+学校
        (year_type,month_type,day_type,line_type,itcast_school_id,itcast_school_name,group_type),               --日期+线上线下+学校
        (year_type,month_type,day_type,line_type,itcast_subject_id,itcast_subject_name,group_type),             --日期+线上线下+学科
        (year_type,month_type,day_type,line_type,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,group_type),      --日期+线上线下+学校+学科
        (year_type,month_type,day_type,line_type,origin_channel,group_type),                             --日期+线上线下+来源渠道
        (year_type,month_type,day_type,line_type,department_id,department_name,group_type)           --日期+线上线下+咨询中心
);
----意向相关
WITH relationship_tmp AS (
    SELECT
           group_type,
           rc_line_type,
           rc_date_type AS day_type,
           rc_signup_count,
           relation_count,
           substring(rc_date_type,1,7) AS month_type,
           substring(rc_date_type,1,4) AS year_type,
           dt
    FROM oe_dws.dws_signup_message1
    WHERE rc_date_type IS NOT NULL
)
SELECT

       year_type,
       month_type,
       day_type,
        CASE
            WHEN grouping(year_type,month_type,day_type) = 0 THEN 'day'
            WHEN grouping(year_type,month_type) = 0 THEN 'month'
            WHEN grouping(year_type) = 0 THEN 'year'
            ELSE 'other' END AS date_type,
        group_type,
        rc_line_type,
        sum(rc_signup_count) AS rc_signup_count,
        sum(relation_count) AS relation_count,
        '2023-05-30' AS dt
FROM relationship_tmp
group by
grouping sets(
    (year_type,rc_line_type,group_type),
    (year_type,month_type,rc_line_type,group_type),
    (year_type,month_type,day_type,rc_line_type,group_type)
);
---线索相关
WITH clue_tmp AS (
    SELECT
           group_type,
           cc_line_type,
           cc_date_type AS day_type,
           cc_sigup_count,
           clue_count,
           substring(cc_date_type,1,7) AS month_type,
           substring(cc_date_type,1,4) AS year_type,
           dt
    FROM oe_dws.dws_signup_message1
    WHERE cc_date_type IS NOT NULL
)
SELECT
       year_type,
       month_type,
       day_type,
        CASE
            WHEN grouping(year_type,month_type,day_type) = 0 THEN 'day'
            WHEN grouping(year_type,month_type) = 0 THEN 'month'
            WHEN grouping(year_type) = 0 THEN 'year'
            ELSE 'other' END AS date_type,
        group_type,
        cc_line_type,
        sum(cc_sigup_count) AS rc_signup_count,
        sum(clue_count) AS relation_count,
        '2023-05-30' AS dt
FROM clue_tmp
group by
grouping sets(
    (year_type,cc_line_type,group_type),
    (year_type,month_type,cc_line_type,group_type),
    (year_type,month_type,day_type,cc_line_type,group_type)
);

--------组合起来

CREATE TABLE oe_dm.tmp4 AS
SELECT year_type,
       month_type,
       day_type,
       date_type,
       group_type_old,
       group_type_new,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       line_type,
       origin_channel,
       department_id,
       department_name,
       group_type_signup_count,
       rc_line_type_old,
       rc_line_type_new,
       rc_signup_count,
       relation_count,
       cc_line_type_old,
       cc_line_type_new,
       cc_signup_count,
       clue_count,
       dt
FROM (
                  WITH tmp_signup AS (
                      WITH signup_tmp AS (
                          SELECT itcast_school_id,
                                 itcast_school_name,
                                 itcast_subject_id,
                                 itcast_subject_name,
                                 line_type,
                                 origin_channel,
                                 department_id,
                                 department_name,
                                 group_type,
                                 group_type_signup_count,
                                 pc_date_type                  AS day_type,
                                 SUBSTRING(pc_date_type, 1, 7) AS month_type,
                                 SUBSTRING(pc_date_type, 1, 4) AS year_type,
                                 dt
                          FROM oe_dws.dws_signup_message1
                          WHERE pc_date_type IS NOT NULL
                      )
                      SELECT CASE
                                 WHEN grouping(year_type, month_type, day_type) = 0 THEN 'day'
                                 WHEN grouping(year_type, month_type) = 0 THEN 'month'
                                 WHEN grouping(year_type) = 0 THEN 'year'
                                 ELSE 'other' END         AS date_type,
                             group_type                   AS group_type_old,
                             CASE
                                 WHEN grouping(line_type, itcast_school_id, itcast_subject_id) = 0
                                     THEN 'line_school_subject'
                                 WHEN grouping(line_type, itcast_subject_id) = 0 THEN 'line_subject'
                                 WHEN grouping(line_type, itcast_school_id) = 0 THEN 'line_school'
                                 WHEN grouping(itcast_school_id) = 0 THEN 'school'
                                 WHEN grouping(line_type, origin_channel) = 0 THEN 'line_origin'
                                 WHEN grouping(line_type, department_id) = 0 THEN 'line_dept'
                                 WHEN grouping(year_type) = 0 THEN 'all'
                                 ELSE 'other' END         AS group_type_new,
                             itcast_school_id,
                             itcast_school_name,
                             itcast_subject_id,
                             itcast_subject_name,
                             line_type,
                             origin_channel,
                             department_id,
                             department_name,
                             SUM(group_type_signup_count) AS group_type_signup_count,
                             day_type,
                             month_type,
                             year_type,
                             '2023-05-30'                 AS dt
                      FROM signup_tmp
                      GROUP BY
                          GROUPING SETS (
                          --年
                          (year_type,group_type),
                          (year_type, itcast_school_id, itcast_school_name, group_type),                       --日期+学校
                          (year_type, line_type, itcast_school_id, itcast_school_name, group_type),            --日期+线上线下+学校
                          (year_type, line_type, itcast_subject_id, itcast_subject_name, group_type),          --日期+线上线下+学科
                          (year_type, line_type, itcast_school_id, itcast_school_name, itcast_subject_id,
                           itcast_subject_name, group_type),                                                   --日期+线上线下+学校+学科
                          (year_type, line_type, origin_channel, group_type),                                  --日期+线上线下+来源渠道
                          (year_type, line_type, department_id, department_name, group_type),                  --日期+线上线下+咨询中心
                          --月
                          (year_type, month_type,group_type),
                          (year_type, month_type, itcast_school_id, itcast_school_name, group_type),           --日期+学校
                          (year_type, month_type, line_type, itcast_school_id, itcast_school_name,
                           group_type),                                                                        --日期+线上线下+学校
                          (year_type, month_type, line_type, itcast_subject_id, itcast_subject_name,
                           group_type),                                                                        --日期+线上线下+学科
                          (year_type, month_type, line_type, itcast_school_id, itcast_school_name, itcast_subject_id,
                           itcast_subject_name, group_type),                                                   --日期+线上线下+学校+学科
                          (year_type, month_type, line_type, origin_channel, group_type),                      --日期+线上线下+来源渠道
                          (year_type, month_type, line_type, department_id, department_name, group_type),      --日期+线上线下+咨询中心
                          --天
                          (year_type, month_type, day_type,group_type),
                          (year_type, month_type, day_type, itcast_school_id, itcast_school_name, group_type), --日期+学校
                          (year_type, month_type, day_type, line_type, itcast_school_id, itcast_school_name,
                           group_type),                                                                        --日期+线上线下+学校
                          (year_type, month_type, day_type, line_type, itcast_subject_id, itcast_subject_name,
                           group_type),                                                                        --日期+线上线下+学科
                          (year_type, month_type, day_type, line_type, itcast_school_id, itcast_school_name,
                           itcast_subject_id, itcast_subject_name, group_type),                                --日期+线上线下+学校+学科
                          (year_type, month_type, day_type, line_type, origin_channel, group_type),            --日期+线上线下+来源渠道
                          (year_type, month_type, day_type, line_type, department_id, department_name,
                           group_type)                                                                         --日期+线上线下+咨询中心
                          )
                  ),
                       tmp_relationship AS (
                           ----意向相关
                           WITH relationship_tmp AS (
                               SELECT DISTINCT
                                      rc_line_type,
                                      rc_date_type                  AS day_type,
                                      rc_signup_count,
                                      relation_count,
                                      SUBSTRING(rc_date_type, 1, 7) AS month_type,
                                      SUBSTRING(rc_date_type, 1, 4) AS year_type,
                                      dt
                               FROM oe_dws.dws_signup_message4
                               WHERE rc_date_type IS NOT NULL
                           )
                           SELECT year_type,
                                  month_type,
                                  day_type,
                                  CASE
                                      WHEN grouping(year_type, month_type, day_type) = 0 THEN 'day'
                                      WHEN grouping(year_type, month_type) = 0 THEN 'month'
                                      WHEN grouping(year_type) = 0 THEN 'year'
                                      ELSE 'other' END                               AS date_type,
                                  rc_line_type                                       AS rc_line_type_old,
                                  if(grouping(rc_line_type) = 0, rc_line_type, NULL) AS rc_line_type_new,
                                  SUM(rc_signup_count)                               AS rc_signup_count,
                                  SUM(relation_count)                                AS relation_count,
                                  '2023-05-30'                                       AS dt
                           FROM relationship_tmp
                           GROUP BY
                               GROUPING SETS ( (year_type, rc_line_type),
                                               (year_type, month_type, rc_line_type),
                                               (year_type, month_type, day_type, rc_line_type)
                               )
                       ),
                       tmp_clue AS (
                           ---线索相关
                           WITH clue_tmp AS (
                               SELECT DISTINCT
                                      cc_line_type,
                                      cc_date_type                  AS day_type,
                                      cc_sigup_count,
                                      clue_count,
                                      SUBSTRING(cc_date_type, 1, 7) AS month_type,
                                      SUBSTRING(cc_date_type, 1, 4) AS year_type,
                                      dt
                               FROM oe_dws.dws_signup_message1
                               WHERE cc_date_type IS NOT NULL
                           )
                           SELECT year_type,
                                  month_type,
                                  day_type,
                                  CASE
                                      WHEN grouping(year_type, month_type, day_type) = 0 THEN 'day'
                                      WHEN grouping(year_type, month_type) = 0 THEN 'month'
                                      WHEN grouping(year_type) = 0 THEN 'year'
                                      ELSE 'other' END                               AS date_type,
                                  cc_line_type                                       AS cc_line_type_old,
                                  if(grouping(cc_line_type) = 0, cc_line_type, NULL) AS cc_line_type_new,
                                  SUM(cc_sigup_count)                                AS cc_signup_count,
                                  SUM(clue_count)                                    AS clue_count,
                                  '2023-05-30'                                       AS dt
                           FROM clue_tmp
                           GROUP BY
                               GROUPING SETS ( (year_type, cc_line_type),
                                               (year_type, month_type, cc_line_type),
                                               (year_type, month_type, day_type, cc_line_type)
                               )
                       )
                  SELECT year_type,
                         month_type,
                         day_type,
                         date_type,
                         group_type_old,
                         group_type_new,
                         itcast_school_id,
                         itcast_school_name,
                         itcast_subject_id,
                         itcast_subject_name,
                         line_type,
                         origin_channel,
                         department_id,
                         department_name,
                         group_type_signup_count,
                         NULL AS rc_line_type_old,
                         NULL AS rc_line_type_new,
                         NULL AS rc_signup_count,
                         NULL AS relation_count,
                         NULL AS cc_line_type_old,
                         NULL AS cc_line_type_new,
                         NULL AS cc_signup_count,
                         NULL AS clue_count,
                         dt
                  FROM tmp_signup
                  WHERE group_type_old = group_type_new
                  UNION ALL
                  SELECT year_type,
                         month_type,
                         day_type,
                         date_type,
                         NULL       AS group_type_old,
                         NULL       AS group_type_new,
                         NULL       AS itcast_school_id,
                         NULL       AS itcast_school_name,
                         NULL       AS itcast_subject_id,
                         NULL       AS itcast_subject_name,
                         NULL       AS line_type,
                         NULL       AS origin_channel,
                         NULL       AS department_id,
                         NULL       AS department_name,
                         NULL       AS group_type_signup_count,
                         rc_line_type_old,
                         rc_line_type_new,
                         rc_signup_count,
                         relation_count,
                         NULL       AS cc_line_type_old,
                         NULL       AS cc_line_type_new,
                         NULL       AS cc_signup_count,
                         NULL       AS clue_count,
                         dt
                  FROM tmp_relationship
                  WHERE rc_line_type_old = rc_line_type_new
                  UNION ALL
                  SELECT year_type,
                         month_type,
                         day_type,
                         date_type,
                         NULL       AS group_type_old,
                         NULL       AS group_type_new,
                         NULL       AS itcast_school_id,
                         NULL       AS itcast_school_name,
                         NULL       AS itcast_subject_id,
                         NULL       AS itcast_subject_name,
                         NULL       AS line_type,
                         NULL       AS origin_channel,
                         NULL       AS department_id,
                         NULL       AS department_name,
                         NULL       AS group_type_signup_count,
                         NULL       AS rc_line_type_old,
                         NULL       AS rc_line_type_new,
                         NULL       AS rc_signup_count,
                         NULL       AS relation_count,
                         cc_line_type_old,
                         cc_line_type_new,
                         cc_signup_count,
                         clue_count,
                         dt
                  FROM tmp_clue
                  WHERE cc_line_type_old = cc_line_type_new
              )
;



---------插入数据dm表

CREATE TABLE oe_dm.dm_signup_message4 AS
SELECT * FROM oe_dm.tmp4;"